SQLite Quick Guide

Expressions:

An expression may be.

Variable

Expression

TotCarbFlow

[CarbonFlow1]+[CarbonFlow2]+[CarbonFlow3]

TotGasFlow

[B1NGASFlow]+[B2NGASFlow]+[B3NGASFlow]

TotO2Flow

[B1MainO2Flow]+[B2MainO2Flow]+[B3MainO2Flow]+[B1SHROUDFlow]+[B2SHROUDFlow]+[B3SHROUDFlow]

HeatWt

CASE WHEN [ChargeNumber]==1 THEN [Ch1Weight] ELSE ([Ch1Weight]+[Ch2Weight]) END

Note the importance to wrap previously defined variables with brackets.

Constant as Expression:

It is possible to define a new variable with a constant value and use it further, just type the value in the expression textbox. Use the variable name in further expressions instead the raw value; if you need to update the constant value just changed the variable expression with the new value (only one place) and regenerate the SQLite views.

SQLite Arithmetic Operators:

Assume variable a holds 10 and variable b holds 20 then:

Operator

Description

Example

+

Addition - Adds values on either side of the operator

a + b will give 30

-

Subtraction - Subtracts right hand operand from left hand operand

a - b will give -10

*

Multiplication - Multiplies values on either side of the operator

a * b will give 200

/

Division - Divides left hand operand by right hand operand

b / a will give 2

%

Modulus - Divides left hand operand by right hand operand and returns remainder

b % a will give 0

 

SQLite Comparison Operators:

Assume variable a holds 10 and variable b holds 20 then:

Operator

Description

Example

==

Checks if the value of two operands are equal or not, if yes then condition becomes true.

(a == b) is not true.

=

Checks if the value of two operands are equal or not, if yes then condition becomes true.

(a = b) is not true.

!=

Checks if the value of two operands are equal or not, if values are not equal then condition becomes true.

(a != b) is true.

<> 

Checks if the value of two operands are equal or not, if values are not equal then condition becomes true.

(a <> b) is true.

> 

Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.

(a > b) is not true.

< 

Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.

(a < b) is true.

>=

Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.

(a >= b) is not true.

<=

Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.

(a <= b) is true.

!<

Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.

(a !< b) is false.

!>

Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

(a !> b) is true.

 

SQLite Logical Operators:

Here is a list of all the logical operators available in SQLite.

Operator

Description

AND

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

BETWEEN

The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

EXISTS

The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.

IN

The IN operator is used to compare a value to a list of literal values that have been specified.

NOT IN

The negation of IN operator which is used to compare a value to a list of literal values that have been specified.

LIKE

The LIKE operator is used to compare a value to similar values using wildcard operators.

GLOB

The GLOB operator is used to compare a value to similar values using wildcard operators. Also, GLOB is case sensitive, unlike LIKE.

NOT

The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator.

OR

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.

IS NULL

The NULL operator is used to compare a value with a NULL value.

IS

The IS operator work like =

IS NOT

The IS operator work like !=

||

Adds two different string and make new one.

UNIQUE

The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

 

SQLite Bitwise Operators:

Bitwise operator works on bits and perform bit by bit operation. The truth table for & and | is as follows:

p

q

p & q

p | q

0

0

0

0

0

1

0

1

1

1

1

1

1

0

0

1

Assume if A = 60; and B = 13; Now in binary format they will be as follows:

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A|B = 0011 1101

~A  = 1100 0011

The Bitwise operators supported by SQLite language are listed in the following table. Assume variable A holds 60 and variable B holds 13 then:

Operator

Description

Example

&

Binary AND Operator copies a bit to the result if it exists in both operands.

(A & B) will give 12 which is 0000 1100

|

Binary OR Operator copies a bit if it exists in either operand.

(A | B) will give 61 which is 0011 1101

~

Binary Ones Complement Operator is unary and has the effect of 'flipping' bits.

(~A ) will give -61 which is 1100 0011 in 2's complement form due to a signed binary number.

<< 

Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand.

A << 2 will give 240 which is 1111 0000

>> 

Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand.

A >> 2 will give 15 which is 0000 1111

 

SQLite Functions:

SQLite has many built-in functions for performing processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form. For more details, you can check official documentation for SQLite:

S.N.

Function & Description

1

SQLite COUNT Function
The SQLite COUNT aggregate function is used to count the number of rows in a database table.

2

SQLite MAX Function
The SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column.

3

SQLite MIN Function
The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

4

SQLite AVG Function
The SQLite AVG aggregate function selects the average value for certain table column.

5

SQLite SUM Function
The SQLite SUM aggregate function allows selecting the total for a numeric column.

6

SQLite RANDOM Function
The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

7

SQLite ABS Function
The SQLite ABS function returns the absolute value of the numeric argument.

8

SQLite UPPER Function
The SQLite UPPER function converts a string into upper-case letters.

9

SQLite LOWER Function
The SQLite LOWER function converts a string into lower-case letters.

10

SQLite LENGTH Function
The SQLite LENGTH function returns the length of a string.

11

SQLite sqlite_version Function
The SQLite sqlite_version function returns the version of the SQLite library.

 

The BETWEEN operator:

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once. The precedence of the BETWEEN operator is the same as the precedence as operators == and != and LIKE and groups left to right.

The CASE expression:

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages.

The optional expression that occurs in between the CASE keyword and the first WHEN keyword is called the "base" expression. There are two basic forms of the CASE expression: those with a base expression and those without.

In a CASE without a base expression, each WHEN expression is evaluated and the result treated as a boolean, starting with the leftmost and continuing to the right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression that evaluates to true. Or, if none of the WHEN expressions evaluate to true, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions are true, then the overall result is NULL.

A NULL result is considered untrue when evaluating WHEN terms.

In a CASE with a base expression, the base expression is evaluated just once and the result is compared against the evaluation of each WHEN expression from left to right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression for which the comparison is true. Or, if none of the WHEN expressions evaluate to a value equal to the base expression, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions produce a result equal to the base expression, the overall result is NULL.

When comparing a base expression against a WHEN expression, the same collating sequence, affinity, and NULL-handling rules apply as if the base expression and WHEN expression are respectively the left- and right-hand operands of an = operator.

If the base expression is NULL then the result of the CASE is always the result of evaluating the ELSE expression if it exists, or NULL if it does not.

Both forms of the CASE expression use lazy, or short-circuit, evaluation.

The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second:

    CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

    CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

For further information please visit the site http://www.sqlite.org/lang_expr.html

Example expressions:

Suppose that listed variables in expressions were already defined and exist.

Variable

Expression

TotCarbFlow

[CarbonFlow1]+[CarbonFlow2]+[CarbonFlow3]

TotGasFlow

[B1NGASFlow]+[B2NGASFlow]+[B3NGASFlow]

TotO2Flow

[B1MainO2Flow]+[B2MainO2Flow]+[B3MainO2Flow]+[B1SHROUDFlow]+[B2SHROUDFlow]+[B3SHROUDFlow]

HeatWt

CASE WHEN [ChargeNumber]==1 THEN [Ch1Weight] ELSE ([Ch1Weight]+[Ch2Weight]) END